﻿using System;
using System.Data;
using System.Data.SqlClient;
using MyHealthByDesign.Domain;
using System.Collections.Generic;

namespace MyHealthByDesign.DataAccess.AccountDA
{
	public class AccountDao : DaoBase
	{
		public AccountDao(string connectionString)
			:base(connectionString)
		{
			
		}

		public User GetUserInfo(string email, string password)
		{
			User user = null;

			using (var conn = new SqlConnection(ConnectionString))
			{
				using (var command = new SqlCommand("dbo.GetUserInfo", conn))
				{
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.AddWithValue("@email", email);
					command.Parameters.AddWithValue("@password", password);

					conn.Open();

					using (var reader = command.ExecuteReader())
					{
						while (reader != null &&reader.HasRows && reader.Read())
						{
							user = new User
									   {
										   UserId = (int) reader["UserId"],
										   Email = reader["Email"].ToString().Trim(),
										   Password = reader["Password"].ToString().Trim(),
										   Firstname = reader["Firstname"].ToString().Trim(),
										   Lastname = reader["Lastname"].ToString().Trim(),
										   Gender = reader["Gender"].ToString().Trim(),
										   Ethnicity = reader["Ethnicity"].ToString().Trim(),
										   StreetAddress = DBNull.Value != reader["StreetAddress"] ? reader["StreetAddress"].ToString().Trim() : string.Empty,
										   City = DBNull.Value != reader["City"] ? reader["City"].ToString().Trim() : string.Empty,
										   State = DBNull.Value != reader["State"] ? reader["State"].ToString().Trim() : string.Empty,
										   Country = DBNull.Value != reader["Country"] ? reader["Country"].ToString().Trim() : string.Empty,
										   MobilePhone = DBNull.Value != reader["MobilePhone"] ? reader["MobilePhone"].ToString().Trim() : string.Empty
									   };
						} 
					} 
				}
			}   

			return user;
		}

		public string GetPassword(string email)
		{
			string password = string.Empty;

			using (var conn = new SqlConnection(ConnectionString))
			{
				using (var command = new SqlCommand("dbo.GetPassword", conn))
				{
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.AddWithValue("@email", email);
					
					conn.Open();

					using (var reader = command.ExecuteReader())
					{
						while (reader != null && reader.HasRows && reader.Read())
						{
							password = reader["Password"].ToString().Trim();
						}
					}
				}
			}

			return password;
		}

		public int RegsiterUser(string lastname, string firstname, string birthDate, string city, string zipCode, string email, string password, string mobile, string textFlag, string sex, string ethnicity)
		{
			int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert New User");

				using (var command = new SqlCommand("dbo.RegisterUser", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@email", email);
						command.Parameters.AddWithValue("@password", password);
						command.Parameters.AddWithValue("@lastname", lastname);
						command.Parameters.AddWithValue("@firstname", firstname);
						command.Parameters.AddWithValue("@birthDate", birthDate);
						command.Parameters.AddWithValue("@gender", sex);
						command.Parameters.AddWithValue("@ethnicity", ethnicity);
						command.Parameters.AddWithValue("@streetAddress", DBNull.Value);
						command.Parameters.AddWithValue("@city", city);
						command.Parameters.AddWithValue("@state", DBNull.Value);
						command.Parameters.AddWithValue("@zipCode", zipCode);
						command.Parameters.AddWithValue("@mobilePhone", !string.IsNullOrWhiteSpace(mobile) ? mobile : "NULL");
						command.Parameters.AddWithValue("@country", "US");
						command.Parameters.AddWithValue("@textFlag", !string.IsNullOrWhiteSpace(textFlag) ? textFlag : "N");

						insertRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return insertRowsCount;
		}

		public List<Question> GetQuestionsForCategory(int categoryID)
		{

			List<Question> lstQ = new List<Question>(); // return variable

			SqlCommand command = null; //command for getting questions
			SqlCommand command2 = null; // command for getting options for a question
			SqlCommand command3 = null; // command for getting special accommodations for an option

			SqlDataAdapter da = null; // data adapter for questions
			SqlDataAdapter da2 = null; // data adapter for options
			SqlDataAdapter da3 = null; // data adapter for specials

			DataTable reader = new DataTable(); // data table for questions
			DataTable reader2 = new DataTable(); // data table for options
			DataTable reader3 = new DataTable(); // data table for specials

			using (SqlConnection conn = new SqlConnection(ConnectionString))
			{
				conn.Open();
				
				command = new SqlCommand("dbo.GetQuestionsByCategory", conn);
				command.CommandType = CommandType.StoredProcedure;
				command.Parameters.AddWithValue("@categoryID", categoryID);
				
				using (da = new SqlDataAdapter(command))
				{
                    reader.Clear();
					da.Fill(reader);

					foreach(DataRow dr in reader.Rows)
					{
                        // create and instance of a questino from data
						Question q = new Question();
						q.Id = (int) dr["QuestionID"];
						q.Category = (int) dr["QuestionCategoryID"];
						q.SortOrder = (int) dr["QuestionSortOrder"];
						q.DisplayType = dr["DisplayType"].ToString().Trim();
						q.Name = dr["FieldName"].ToString().Trim();
						q.ContentLabel = dr["FieldLabel"].ToString().Trim();
						q.FieldCSS = DBNull.Value != dr["FieldCSS"]
										 ? dr["FieldCSS"].ToString().Trim()
										 : null;
						q.FieldSuffix = DBNull.Value != dr["FieldSuffix"]
											? dr["FieldSuffix"].ToString().Trim()
											: null;
						q.FieldHelpText = DBNull.Value != dr["FieldHelpText"]
											  ? dr["FieldHelpText"].ToString().Trim()
											  : null;
						q.FieldHelpURL = DBNull.Value != dr["FieldHelpURL"]
											 ? dr["FieldHelpURL"].ToString().Trim()
											 : null;

						// get options for question
						List<QuestionOption> lstQO = new List<QuestionOption>();

						command2 = new SqlCommand("dbo.GetOptionsByQuestion", conn);
						command2.CommandType = CommandType.StoredProcedure;
						command2.Parameters.AddWithValue("@questionID", q.Id);
						
						using(da2 = new SqlDataAdapter(command2))
						{
                            reader2.Clear();
							da2.Fill(reader2);

							foreach(DataRow dr2 in reader2.Rows)
							{
								QuestionOption qo = new QuestionOption();

								qo.Id = (int)dr2["OptionID"];
								qo.Name = dr2["OptionName"].ToString().Trim();
								qo.SortOrder = (int)dr2["OptionSortOrder"];
								qo.OptionLabel = dr2["OptionLabel"].ToString().Trim();

								//get specials for option
								List<OptionSpecial> lstOS = new List<OptionSpecial>();

								command3 = new SqlCommand("dbo.GetSpecialsByOption", conn);
								command3.CommandType = CommandType.StoredProcedure;
								command3.Parameters.AddWithValue("@optionID", qo.Id);

								using (da3 = new SqlDataAdapter(command3))
								{
                                    reader3.Clear();
									da3.Fill(reader3);

									foreach (DataRow dr3 in reader3.Rows)
									{
										OptionSpecial os = new OptionSpecial();

										os.Id = (int)dr3["SpecialID"];
										os.Name = dr3["SpecialName"].ToString().Trim();
										os.DisplayType = dr3["SpecialDisplayType"].ToString().Trim();

										lstOS.Add(os);
									}

								}

								qo.Specials = lstOS; // add specials collection to option

								lstQO.Add(qo); 
							}
						}

						q.FieldOptions = lstQO; // add options collection to question

						lstQ.Add(q); // add question to output variable
					}
				}
				
				conn.Close();
			}

			return lstQ;
		}

		public List<QuestionAnswer> GetUserAnswers(int userId)
		{
			var questionAnswerLists = new List<QuestionAnswer>();

			using (var conn = new SqlConnection(ConnectionString))
			{
				using (var command = new SqlCommand("dbo.GetUserAnswers", conn))
				{
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.AddWithValue("@userID", userId);

					conn.Open();

					using (var reader = command.ExecuteReader())
					{
						while (reader != null && reader.HasRows && reader.Read())
						{
							var questionAnswer = new QuestionAnswer
													 {
														 AnswerId = (int) reader["AnswerID"],
														 QuestionId = (int) reader["QuestionID"],
														 UserId = userId,
														 FieldName = (string) reader["FieldName"],
														 FieldValue = (string) reader["FieldValue"]
													 };
							questionAnswerLists.Add(questionAnswer);
						}
					}
				}
			}

			return questionAnswerLists;
		}

		public int InsertOptionSpecial(int optionId, string specialName, string specialDisplayType)
		{
			int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.InsertOptionSpecial", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@optionID", optionId);
						command.Parameters.AddWithValue("@specialName", specialName);
						command.Parameters.AddWithValue("@specialDisplayType", specialDisplayType);

						insertRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return insertRowsCount;
		}

		public int InsertQuestion(int questionCategoryId, int questionSortOrder, string displayType, string fieldName,
								  string fieldLabel, string fieldCss, string fieldSuffix, string fieldHelpText, string fieldHelpUrl)
		{
	
			int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.InsertQuestion", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionCategoryID", questionCategoryId);
						command.Parameters.AddWithValue("@questionSortOrder", questionSortOrder);
						command.Parameters.AddWithValue("@displayType", displayType);
						command.Parameters.AddWithValue("@fieldName", fieldName);
						command.Parameters.AddWithValue("@fieldLabel", fieldLabel);
						command.Parameters.AddWithValue("@fieldCSS", fieldCss);
						command.Parameters.AddWithValue("@fieldSuffix", fieldSuffix);
						command.Parameters.AddWithValue("@fieldHelpText", fieldHelpText);
						command.Parameters.AddWithValue("@fieldHelpUrl", fieldHelpUrl);

						insertRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return insertRowsCount;
		}

		public int InsertQuestionOption(int questionId, string optionName, int optionSortOrder, string optionLabel)
		{
			int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.InsertQuestionOption", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionID", questionId);
						command.Parameters.AddWithValue("@optionName", optionName);
						command.Parameters.AddWithValue("@optionSortOrder", optionSortOrder);
						command.Parameters.AddWithValue("@optionLabel", optionLabel);

						insertRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return insertRowsCount;
		}

		public bool InsertUserAnswers(List<QuestionAnswer> lstQa)
		{
		    bool bSuccess = false;
		    int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.InsertUserAnswers", conn, transaction))
				{
					try
					{
					    foreach (QuestionAnswer qa in lstQa)
					    {
					    
					        command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Clear();
					        command.Parameters.AddWithValue("@questionID", qa.QuestionId);
					        command.Parameters.AddWithValue("@userID", qa.UserId);
					        command.Parameters.AddWithValue("@fieldName", qa.FieldName);
					        command.Parameters.AddWithValue("@fieldValue", qa.FieldValue);

					        insertRowsCount = command.ExecuteNonQuery();

                            if(insertRowsCount == 0)
                                throw new Exception("Not Inserted");
					    }
				        transaction.Commit();
					    bSuccess = true;
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return bSuccess;
		}

		public int UpdateQuestion(int questionId, int questionCategoryId, int questionSortOrder, string displayType,
								  string fieldLabel, string fieldCss, string fieldSuffix, string fieldHelpText, string fieldHelpUrl)
		{

			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestion", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionID", questionId);
						command.Parameters.AddWithValue("@questionCategoryID", questionCategoryId);
						command.Parameters.AddWithValue("@questionSortOrder", questionSortOrder);
						command.Parameters.AddWithValue("@displayType", displayType);
						command.Parameters.AddWithValue("@fieldLabel", fieldLabel);
						command.Parameters.AddWithValue("@fieldCSS", fieldCss);
						command.Parameters.AddWithValue("@fieldSuffix", fieldSuffix);
						command.Parameters.AddWithValue("@fieldHelpText", fieldHelpText);
						command.Parameters.AddWithValue("@fieldHelpUrl", fieldHelpUrl);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

		public int UpdateQuestionActiveStatus(int questionId)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionActiveStatus", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionID", questionId);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

		public int UpdateQuestionOption(int optionId, int optionSortOrder, string optionLabel)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionOption", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@optionID", optionId);
						command.Parameters.AddWithValue("@optionSortOrder", optionSortOrder);
						command.Parameters.AddWithValue("@optionLabel", optionLabel);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

		public int UpdateQuestionOptionActiveStatus(int optionId)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionOptionActiveStatus", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@optionID", optionId);
						
						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

		public int UpdateQuestionOptionSpecial(int specialId, string specialDisplayType)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionOptionSpecial", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@specialID", specialId);
						command.Parameters.AddWithValue("@specialDisplayType", specialDisplayType);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

		public int UpdateUserAnswers(int answerId, string fieldName, string fieldValue)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateUserAnswers", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@answerID", answerId);
						command.Parameters.AddWithValue("@fieldName", fieldName);
						command.Parameters.AddWithValue("@fieldValue", fieldValue);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}
	}
}